package bank.model.dao.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import bank.model.dao.AccountDAO;
import bank.model.pool.DBConnectionPool;
import bank.model.properties.DBTables;
import bank.model.sql.builder.SqlString;
import bank.model.vo.Account;

public class MySqlAccountDAO implements AccountDAO,DBTables {
	
	private DBConnectionPool connectionPool = DBConnectionPool.getInstance();
	private SqlString sql = new SqlString();
	
	
	public List findAll() throws Exception {
		
		List accounts= new ArrayList();
		sql.clear_All();
		sql.set_Select();
		sql.set_Table_Name(TBL_ACCOUNTS);
		sql.set_all();		
		sql.generate();
		
		Connection con = connectionPool.getConnection();		
		Statement s = con.prepareStatement(sql.get_Command());
		ResultSet resultSet = s.executeQuery(sql.get_Command());
				
		while (resultSet.next()) {
			Account account = new Account();			
			account.setId(resultSet.getInt(ACCOUNT_ID));
			account.setAmount(resultSet.getInt(ACCOUNT_AMOUNT));
			account.setClientID(resultSet.getInt(ACCOUNT_CLIENT_ID));					
			accounts.add(account);
		}		
		connectionPool.freeConnection(con);
		
		return accounts;
	}

	public List findByClientId(int clientId) throws Exception {
		
		List accounts= new ArrayList();		
		sql.clear_All();		
		sql.set_Select();
		sql.set_all();
		sql.set_Table_Name(TBL_ACCOUNTS);
		sql.add_Cond(ACCOUNT_CLIENT_ID, Integer.toString(clientId));
		sql.generate();		
		
		Connection con = connectionPool.getConnection();		
		Statement s = con.prepareStatement(sql.get_Command());
		ResultSet resultSet = s.executeQuery(sql.get_Command());		
		
		while (resultSet.next()) {
			Account account = new Account();			
			account.setId(resultSet.getInt(ACCOUNT_ID));
			account.setAmount(resultSet.getInt(ACCOUNT_AMOUNT));
			account.setClientID(resultSet.getInt(ACCOUNT_CLIENT_ID));
					
			accounts.add(account);
		}		
		connectionPool.freeConnection(con);
		return accounts;
	}
	

	public void add(int amount, int clientId) throws Exception {
		
		sql.clear_All();
		sql.set_Insert();
		sql.set_Table_Name(TBL_ACCOUNTS);
		sql.add_Param(ACCOUNT_AMOUNT,  Integer.toString(amount));		
		sql.add_Param(ACCOUNT_CLIENT_ID, Integer.toString(clientId));		
		sql.generate();
		
		Connection con = connectionPool.getConnection();		
		Statement s = con.prepareStatement(sql.get_Command());
		s.executeUpdate(sql.get_Command());	
					
		connectionPool.freeConnection(con);
		
	}

	public void delete(	int id) throws Exception {
		
		sql.clear_All();
		sql.set_Delete();
		sql.set_Table_Name(TBL_ACCOUNTS);
		sql.add_Cond(ACCOUNT_ID, Integer.toString(id));
		sql.generate();
		
		Connection con = connectionPool.getConnection();		
		Statement s = con.prepareStatement(sql.get_Command());
		s.executeUpdate(sql.get_Command());	
					
		connectionPool.freeConnection(con);		
		
	}
	

	public void update(int id,Account account) throws Exception {
		sql.clear_All();
		sql.set_Update();
		sql.set_Table_Name(TBL_DEPARTMENTS);
		sql.add_Cond(ACCOUNT_ID, Integer.toString(id));
		sql.add_Param(ACCOUNT_AMOUNT, Integer.toString(account.getAmount()));
		
				
		Connection con = connectionPool.getConnection();		
		Statement s = con.prepareStatement(sql.get_Command());
		s.executeUpdate(sql.get_Command());	
		
		connectionPool.freeConnection(con);
		
		
		
	}

	

}
